﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Objects;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MINERP.DAL.Statistics
{
    public class StatisticsDAL : DALBase
    {
        #region 获取商户集合
        /// <summary>
        /// 获取商户集合
        /// </summary>
        /// <returns></returns>
        public ObjectQuery<DbDataRecord> GetShopList()
        {
            try
            {
                string esql = "select it.ShopId,it.shopName from Shop as it";
                ObjectQuery<DbDataRecord> query = DataEntites.CreateQuery<DbDataRecord>(esql);
                return query;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 查询指定条件的账单
        /// <summary>
        /// 查询指定条件的账单
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<SHOP_Bill> GetShopBill(int shopId, DateTime startTime, DateTime endTime)
        {
            try
            {
                IQueryable<SHOP_Bill> query = DataEntites.SHOP_Bill.Where(p => p.Shop.ShopId == shopId && p.CreateTime >= startTime && p.CreateTime <= endTime);
                List<SHOP_Bill> ListBill = new List<SHOP_Bill>();
                foreach (var b in query)
                {
                    ListBill.Add(b as SHOP_Bill);
                }
                return ListBill;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 某个商户，某段时间内未结单的账单
        /// <summary>
        /// 某个商户，某段时间内未结单的账单
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public List<SHOP_Bill> GetShopBillNoPay(int shopId, DateTime startTime, DateTime endTime, int status)
        {
            try
            {
                IQueryable<SHOP_Bill> query = DataEntites.SHOP_Bill.Where(p => p.Shop.ShopId == shopId &&p.Status==status && p.CreateTime >= startTime && p.CreateTime <= endTime);
                List<SHOP_Bill> ListBill = new List<SHOP_Bill>();
                foreach (var b in query)
                {
                    ListBill.Add(b as SHOP_Bill);
                }
                return ListBill;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据billId获取账单
        /// <summary>
        /// 根据billId获取账单
        /// </summary>
        /// <param name="billId"></param>
        /// <returns></returns>
        public SHOP_Bill GetBills(int billId)
        {
            try
            {
                SHOP_Bill sb = DataEntites.SHOP_Bill.Where(p => p.BillId == billId).First();
                return sb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取账单下的产品集合
        /// <summary>
        /// 获取账单下的产品集合
        /// </summary>
        /// <param name="billId"></param>
        /// <returns></returns>
        public List<Bill_Detail> GetBillDetails(int billId)
        {
            try
            {
                IQueryable<Bill_Detail> bds = DataEntites.Bill_Detail.Where(p => p.SHOP_Bill.BillId == billId);
                List<Bill_Detail> billDetails = new List<Bill_Detail>();
                foreach (var bd in bds)
                {
                    billDetails.Add(bd);
                }
                return billDetails;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取产品的账单详情
        /// <summary>
        /// 获取产品的账单详情
        /// </summary>
        /// <param name="billDetailId"></param>
        /// <returns></returns>
        public List<Bill_Detail_Detail> GetBillDetailDetails(int billDetailId)
        {
            try
            {
                IQueryable<Bill_Detail_Detail> bdds = DataEntites.Bill_Detail_Detail.Where(p => p.Bill_Detail.BillDetailId == billDetailId);
                List<Bill_Detail_Detail> billDetailDetails = new List<Bill_Detail_Detail>();
                foreach (var bdd in bdds)
                {
                    billDetailDetails.Add(bdd);
                }
                return billDetailDetails;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion 

        #region 获取指定的商户
        /// <summary>
        /// 获取指定的商户
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public Shop GetShopByShopId(int shopId)
        {
            try
            {
                Shop s = DataEntites.Shop.Where(p => p.ShopId == shopId).First();
                return s;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion 
        
        #region 获取具体某个产品的金额
        /// <summary>
        /// 获取具体某个产品的金额
        /// </summary>
        /// <param name="billDetailId"></param>
        /// <returns></returns>
        public double GetPriceOfAOrderInfo(int billDetailId)
        {
            try
            {
                List<Bill_Detail_Detail> ListDetail = DataEntites.Bill_Detail_Detail.Where(p => p.Bill_Detail.BillDetailId == billDetailId).ToList<Bill_Detail_Detail>();
                double m = 0;
                foreach (Bill_Detail_Detail bdd in ListDetail)
                {
                    m += bdd.Count;
                }
                return m;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取一个产品的总面积
        /// <summary>
        /// 获取一个产品的总面积
        /// </summary>
        /// <param name="orderInfoCode"></param>
        /// <returns></returns>
        public static double GetAreaOfAOrderInfo(string orderInfoCode)
        {
            try
            {
                List<photoInfo> ListPhoto = DataEntites.photoInfo.Where(p => p.orderInfo.OrderProductCode == orderInfoCode).ToList();
                double area = 0;
                foreach (photoInfo photo in ListPhoto)
                {
                    double h = photo.Height * 2.54 / 100;
                    double w = photo.Width * 2.54 / 100;
                    area += h * w;                          //结果按平方米统计
                }
                return area;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取一段时间内所有的BillDetail
        /// <summary>
        /// 获取一段时间内所有的BillDetail
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<Bill_Detail> GetListBillDetail(DateTime startTime, DateTime endTime)
        {
            try
            {
                List<Bill_Detail> listBillDetail = DataEntites.Bill_Detail.Where(p => p.SHOP_Bill.CreateTime > startTime && p.SHOP_Bill.CreateTime < endTime).ToList<Bill_Detail>();
                return listBillDetail;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据产品编号获取其产品模板
        /// <summary>
        /// 根据产品编号获取其产品模板
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Tb_Product GetProductByOrderInfoCode(string code)
        {
            try
            {
                orderInfo info = DataEntites.orderInfo.Where(p => p.OrderProductCode == code).FirstOrDefault();
                if (info == null)
                    return null;
                //Tb_Product product = DataEntites.Tb_Product.Where(p => p.orderInfo.Contains (info)).First();
                if (!info.Tb_ProductReference.IsLoaded)
                    info.Tb_ProductReference.Load();
                return info.Tb_Product;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取所有商户
        /// <summary>
        /// 获取所有商户
        /// </summary>
        /// <returns></returns>
        public List<Shop> GetAllShop()
        {
            try
            {
                List<Shop> listShop = DataEntites.Shop.ToList<Shop>();
                return listShop;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取指定商户在一段时间内未结单的账单ID集合
        /// <summary>
        /// 获取指定商户在一段时间内未结单的账单ID集合
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public List<int> GetBillIds(int shopId,int status,DateTime startTime,DateTime endTime)
        {
            try
            {
                List<SHOP_Bill> Bills = DataEntites.SHOP_Bill.Where(p => p.Shop.ShopId == shopId && p.Status == status&&p.CreateTime>startTime&&p.CreateTime<endTime).ToList<SHOP_Bill>();
                List<int> BillIds = new List<int>();
                if (Bills == null || Bills.Count == 0)
                    return null;
                foreach (SHOP_Bill sb in Bills)
                {
                    BillIds.Add(sb.BillId);
                }
                return BillIds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        //----------------------------------------------------------------------------

        
        private static ObjectQuery<Shop> GetShops()
        {
            return DataEntites.Shop.Where("it.Status =1");
        }


        /// <summary>
        /// Get Shop list
        /// </summary>
        /// <returns></returns>
        public static List<Shop> GetShopObjList()
        {
            List<Shop> list = new List<Shop>();
            foreach (Shop shop in GetShops().OrderBy(s=>s.shopName))
            {
                list.Add(shop);
            }
            return list;
        }
        public static Shop GetShopObj(int id)
        {   
            return DataEntites.Shop.Where(p => p.ShopId == id).First();
        }

        public static Shop GetShopObj(string name)
        {
            return DataEntites.Shop.Where(p => p.shopName == name).First();
        }

        public static SHOP_Bill GetBillObj(int id)
        {
            return DataEntites.SHOP_Bill.First(b => b.BillId == id);
        }
        public static orderInfo GetOrderInfo(int id)
        {
            return DataEntites.orderInfo.First(o =>o.OrderProductId ==id);
        }
        public static void Update()
        {
            DataEntites.SaveChanges();
        }


        public DataSet GetSalemanOutReturnOrder(string salesman,DateTime startTime, DateTime endTime)
        {
            DataSet dsResult = new DataSet();
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("GetSalesmanOutReturnOrder");
            db.AddInParameter(dbCommand, "salesmanName", DbType.String, salesman);
            db.AddInParameter(dbCommand, "startTime", DbType.DateTime, startTime);
            db.AddInParameter(dbCommand, "endTime", DbType.DateTime, endTime);

            try
            {
                dsResult = db.ExecuteDataSet(dbCommand);
            }
            catch (Exception)
            {
                return null;
            }

            return dsResult;
        }
    }
}
